package ye.example.poi;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;

public class PoiController {
    /**
     * HSSF - Excel.xls 2003 版本(有最大行数限制)
     * XSSF - Excel.xlsx OOXML 2007版本(无最大版本限制)
     * HWPF - Word 处理
     * HSLF - PowerPoint 处理
     * HDGF - Visio 处理
     */
    @Test
    public void writer() throws IOException {
        // 建工作簿
        // 2003 版本
        Workbook workbook = new HSSFWorkbook();
        String excelType = "/poi.xls";
        // 2007 版本
        // Workbook workbook = new XSSFWorkbook();
        // String excelType = "/poi.xlsx";
        // 2007 版本 用于处理大量数据(会对数据进行缓存)
        // Workbook workbook = new SXSSFWorkbook();
        // String excelType = "/poi.xlsx";
        // 建表
        Sheet sheet = workbook.createSheet("表一");
        // 建立第一行
        Row row1 = sheet.createRow(0);
        Cell cell00 = row1.createCell(0);
        cell00.setCellValue("单元格 100");
        Cell cell01 = row1.createCell(1);
        cell01.setCellValue("单元格 101");
        // 建立第二行
        Row row2 = sheet.createRow(1);
        Cell cell10 = row2.createCell(0);
        cell10.setCellValue("单元格200");
        Cell cell11 = row2.createCell(1);
        cell11.setCellValue("单元格201");
        // 输出文件
        String path = System.getProperty("user.dir");
        FileOutputStream file = new FileOutputStream(path + excelType);
        workbook.write(file);
        file.close();
    }


    /**
     * 需要注意Excel中的数据格式是多样的，需要自己进行处理
     */
    @Test
    public void reader() throws IOException {
        String excelType = "/poi.xls";
        String path = System.getProperty("user.dir");
        FileInputStream file = new FileInputStream(path + excelType);
        // 2003 版本
        Workbook workbook = new HSSFWorkbook(file);
        // 2007 版本
        // Workbook workbook = new XSSFWorkbook(file);
        // String excelType = "/poi.xlsx";
        // 2007 版本 用于处理大量数据(会对数据进行缓存)
        // Workbook workbook = new SXSSFWorkbook(file);
        // String excelType = "/poi.xlsx";
        // 获取表格
        Sheet sheet1 = workbook.getSheetAt(0);
        // 获取行
        Row row1 = sheet1.getRow(0);
        // 获取单元格
        Cell cell00 = row1.getCell(0);
        Cell cell01 = row1.getCell(1);
        // 输出内容
        System.out.println(cell00.getStringCellValue());
        System.out.println(cell01.getStringCellValue());
        file.close();
    }

    /**
     * Excel文件遍历读取进阶，对类型和null判断
     */
    @Test
    public void readerAdvance() throws IOException {
        String excelType = "/poi.xls";
        String path = System.getProperty("user.dir");
        FileInputStream file = new FileInputStream(path + excelType);
        // 2003 版本
        Workbook workbook = new HSSFWorkbook(file);
        // 2007 版本
        // Workbook workbook = new XSSFWorkbook(file);
        // String excelType = "/poi.xlsx";
        // 2007 版本 用于处理大量数据(会对数据进行缓存)
        // Workbook workbook = new SXSSFWorkbook(file);
        // String excelType = "/poi.xlsx";
        // 获取表格
        for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
            Sheet sheetCurrent = workbook.getSheetAt(sheetIndex);
            for (int rowIndex = 0; rowIndex < sheetCurrent.getPhysicalNumberOfRows(); rowIndex++) {
                Row currentRow = sheetCurrent.getRow(rowIndex);
                for (int cellIndex = 0; cellIndex < currentRow.getPhysicalNumberOfCells(); cellIndex++) {
                    Cell cellCurrent = currentRow.getCell(cellIndex);
                    if (cellCurrent != null) {
                        String cellValue = null;
                        switch (cellCurrent.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                System.out.print("【STRING】");
                                cellValue = cellCurrent.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                System.out.print("【BOOLEAN】");
                                cellValue =
                                        String.valueOf(cellCurrent.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://空
                                System.out.print("【BLANK】");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                System.out.print("【NUMERIC】");
                                String.valueOf(cellCurrent.getNumericCellValue());
                                if (HSSFDateUtil.isCellDateFormatted(cellCurrent)) {//
                                    System.out.print("【日期】");
                                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                    // 进行格式化 1970-01-01-08-00-00
                                    cellValue = simpleDateFormat.format(cellCurrent.getDateCellValue());
                                } else {
                                    System.out.print("【转换成字符串】");
                                    cellCurrent.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cellCurrent.toString();
                                }
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                System.out.print("【数据类型错误】");
                                break;
                        }
                        System.out.println("单元格值：" + cellValue);
                    }
                }
            }
        }
        file.close();
    }

    @Test
    public void formula() throws Exception {
        String excelType = "/poi.xls";
        String path = System.getProperty("user.dir");
        InputStream file = new FileInputStream(path + excelType);
        Workbook workbook = new HSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第五行第一列
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);
        //公式计算器
        FormulaEvaluator formulaEvaluator = new
                HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        // 输出单元内容
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_FORMULA://2
                //得到公式
                String formula = cell.getCellFormula();
                System.out.println(formula);
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                //String cellValue = String.valueOf(evaluate.getNumberValue());
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
    }
}

